 /**
 *
 * @author Fabio
 */
package Persistencia;
    
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
 
public class usuarioDAO extends conexao{
    public int numRows;

    public usuarioDAO() {
        super();
    }
    
    public int buscarLoginData(BO.usuarioBO uBO) throws Exception {
        String sql;
        ResultSet rs = null;
        
        this.conecta();
        sql = "SELECT * FROM usuario WHERE login = '"+uBO.getLoginBO()+"' and senha = '"+uBO.getSenhaBO()+"'";
        this.sqlSelect(sql);
        rs = this.resultset;
        if (rs.next()) {
                uBO.setMatriculaBO(rs.getInt("matricula"));
                uBO.setLoginBO(rs.getString("login"));
                uBO.setSenhaBO(rs.getString("senha"));
                uBO.setNomeBO(rs.getString("nome"));
                uBO.setEmailBO(rs.getString("email"));
                uBO.setCargoBO(rs.getInt("cargo"));
                numRows++;
        } else {
                uBO.setLoginBO("");
                uBO.setSenhaBO("");
        }
        this.desconectar();
        updateUsuarioStatus(uBO, true);
        if(rs != null) {
            rs.close();
            return 1;
        }
        return 0;
    }
    
    public int buscarUsuariosOnline() throws SQLException{
        String sql;
        ResultSet rs = null;
        int var = 0;
        
        this.conecta();
        sql = "SELECT count(*) as online FROM usuario WHERE online = 1";
        this.sqlSelect(sql);
        rs = this.resultset;
        if(rs.next()){
            var = rs.getInt("online");
        }
        this.desconectar();
        //JOptionPane.showMessageDialog(null, "Teste: "+var);
        if(rs != null) {
            rs.close();
            return var;
        }
        return 999;
    }
    
    public void updateUsuarioStatus(BO.usuarioBO uBO, boolean flag){
        this.conecta();
        if(flag)
        {
            this.executeSQL("UPDATE usuario SET online=1 where login = '"+uBO.getLoginBO()+"'");
        } else {
            this.executeSQL("UPDATE usuario SET online=0 where login = '"+uBO.getLoginBO()+"'");
        }
        this.desconectar();
    }
    
    public void updateSenhaAdmin(BO.usuarioBO uBO) throws SQLException{
        String sql;
        ResultSet rs = null;
        
        this.conecta();
        sql = "UPDATE usuario SET senha = '"+uBO.getSenhaBO()+"' "
                + "where login = '"+uBO.getLoginBO()+"'";
        this.executeSQL(sql);
        this.desconectar();
    }
    
    public void updateSenha(BO.usuarioBO u) throws Exception {
        String sql;

        this.conecta();
        sql = "UPDATE usuario SET senha = '"+u.getNovaSenhaBO()+"' WHERE login = '"+u.getLoginBO()+"';";
        this.executeSQL(sql);
        this.desconectar();
    }
    
        public void updateUsuario(BO.usuarioBO u) throws Exception {
        String sql;

        this.conecta();
        sql = "UPDATE usuario SET nome = '"+u.getNomeBO()+"', "
                + "matricula = '"+u.getMatriculaBO()+"', "
                + "email = '"+u.getEmailBO()+"', "
                + "cargo = '"+u.getCargoBO()+"' "
                + "WHERE login = '"+u.getLoginBO()+"'";
        this.executeSQL(sql);
        this.desconectar();
    } 
    
    public void salvaUsuario(BO.usuarioBO u) throws Exception {
        String sql;

        this.conecta();
        sql = "INSERT INTO usuario VALUES ("
                + "'"+u.getMatriculaBO()+"',"
                + "'"+u.getLoginBO()+"',"
                + "'"+u.getSenhaBO()+"',"
                + "'"+u.getNomeBO()+"',"
                + "'"+u.getEmailBO()+"',"
                + "'"+u.getCargoBO()+"',"
                + "0)";
        this.executeSQL(sql);
        this.desconectar();
    }
    
    public void excluirUsuario(BO.usuarioBO u) throws Exception {
        String sql;
        ResultSet rs = null;

        this.conecta();
        sql = "Delete from usuario WHERE nome = '"+u.getNomeBO()+"'";
        this.executeSQL(sql);
        JOptionPane.showMessageDialog(null, "Usuário deletado com sucesso!");
    }    
    
    public void buscarUsuario(BO.usuarioBO u) throws Exception {
        String sql;
        ResultSet rs = null;

        this.conecta();
        sql = "SELECT * FROM usuario WHERE nome = '"+u.getNomeBO()+"'";
        this.sqlSelect(sql);
        rs = this.resultset;
        if (rs.next()) {
                u.setMatriculaBO(rs.getInt("matricula"));
                u.setLoginBO(rs.getString("login"));
                u.setSenhaBO(rs.getString("senha"));
                u.setNomeBO(rs.getString("nome"));
                u.setEmailBO(rs.getString("email"));
                u.setCargoBO(rs.getInt("cargo"));
        }
        this.desconectar();
        if(rs != null) rs.close();
    }
    
    public void buscarUsuarioLogin(BO.usuarioBO u) throws Exception {
        String sql;
        ResultSet rs = null;
        
        this.conecta();
        sql = "SELECT * FROM usuario WHERE login = '"+u.getLoginBO()+"' and senha = '"+u.getSenhaBO()+"'";
        this.sqlSelect(sql);
        rs = this.resultset;
        if (rs.next()) {
                u.setMatriculaBO(rs.getInt("matricula"));
                u.setLoginBO(rs.getString("login"));
                u.setSenhaBO(rs.getString("senha"));
                u.setNomeBO(rs.getString("nome"));
                u.setEmailBO(rs.getString("email"));
                u.setCargoBO(rs.getInt("cargo"));
        }
        this.desconectar();
        if(rs != null) rs.close();
    }
    
        public void buscarPorLogin(BO.usuarioBO u) throws Exception {
        String sql;
        ResultSet rs = null;
        
        this.conecta();
        sql = "SELECT * FROM usuario WHERE login = '"+u.getLoginBO()+"'";
        this.sqlSelect(sql);
        rs = this.resultset;
        if (rs.next()) {
                u.setMatriculaBO(rs.getInt("matricula"));
                u.setLoginBO(rs.getString("login"));
                u.setSenhaBO(rs.getString("senha"));
                u.setNomeBO(rs.getString("nome"));
                u.setEmailBO(rs.getString("email"));
                u.setCargoBO(rs.getInt("cargo"));
        }
        this.desconectar();
        if(rs != null) rs.close();
    }
 
        public void buscarPorMatricula(BO.usuarioBO u) throws Exception {
        String sql;
        ResultSet rs = null;
        
        this.conecta();
        sql = "SELECT * FROM usuario WHERE matricula = '"+u.getMatriculaBO()+"'";
        this.sqlSelect(sql);
        rs = this.resultset;
        if (rs.next()) {
                u.setMatriculaBO(rs.getInt("matricula"));
                u.setLoginBO(rs.getString("login"));
                u.setSenhaBO(rs.getString("senha"));
                u.setNomeBO(rs.getString("nome"));
                u.setEmailBO(rs.getString("email"));
                u.setCargoBO(rs.getInt("cargo"));
        }
        this.desconectar();
        if(rs != null) rs.close();
    }
        
}
